﻿using Blm.Utils.Extensions;
using ClosedXML.Excel;
using ClosedXML.Excel.Drawings;
using ClosedXML.Report;
using Magicodes.ExporterAndImporter.Core;
using Magicodes.ExporterAndImporter.Excel;
using Microsoft.AspNetCore.Hosting;
using Microsoft.Extensions.Logging;
using NPOI.SS.UserModel;
using NPOI.XSSF.UserModel;
using StackExchange.Redis;
using System;
using System.Collections.Concurrent;
using System.Collections.Generic;
using System.IO;
using System.Linq;
using System.Text;
using System.Text.RegularExpressions;
using System.Threading.Tasks;
using VisionCloud.Domain.DtoModel.Ms;
using VisionCloud.Domain.Repository.Interface.Ms;
using VisionCloud.Utility.Redis;

namespace VisionCloud.Service.Ms
{
    /// <summary>
    /// 分户报告服务
    /// </summary>
    public class ApartmentReportService
    {
        public IWebHostEnvironment Env { get; set; }

        public IMsTaskRepository MsTaskRepository { get; set; }

        public IMsTaskPointsRepository MsTaskPointsRepository { get; set; }

        public ILogger<ApartmentReportService> Logger { get; set; }

        /// <summary>
        /// 缓存Key
        /// </summary>
        /// <param name="taskId"></param>
        /// <returns></returns>
        private string GetReportCacheKey(Guid taskId) => $"task_apartmentreport_{taskId}";

        /// <summary>
        /// 删除缓存
        /// </summary>
        /// <param name="taskId"></param>
        /// <returns></returns>
        public bool DeleteReportCache(Guid taskId)
        {
            var key = GetReportCacheKey(taskId);
            return RedisHelper.Instance.DeleteKey(key);
        }

        /// <summary>
        /// 设置缓存
        /// </summary>
        /// <param name="key"></param>
        /// <param name="reportUrl"></param>
        /// <param name="reportFile"></param>
        public void SetReportCache(string key, string reportUrl, string reportFile)
        {
            List<HashEntry> list = new List<HashEntry>();
            HashEntry hashEntry1 = new HashEntry("ReportUrl", reportUrl);
            HashEntry hashEntry2 = new HashEntry("ReportFile", reportFile);
            list.Add(hashEntry1);
            list.Add(hashEntry2);
            RedisHelper.Instance.HashSet(key, list.ToArray());
            RedisHelper.Instance.Expire(key, TimeSpan.FromDays(1));
        }

        /// <summary>
        /// 批量获取分户报告
        /// </summary>
        /// <param name="ids"></param>
        /// <returns></returns>
        public async ValueTask<List<string>> GetReportUrl(List<Guid> ids)
        {
            ConcurrentBag<string> bag = new ConcurrentBag<string>();
            // net core 3.1版本中Parallel.ForEach不支持异步操作等待结果，改为使用Task.WhenAll 代替 Parallel.ForEach
            // .net6版本中Parallel.ForEachAsync支持异步操作等待结果
            await Task.WhenAll(ids.Select(async id =>
            {
                try
                {
                    var url = await GetReportUrl(id);
                    if (!string.IsNullOrEmpty(url))
                        url = url + $"?randomno={DateTime.Now.ToUnixDateTime()}";

                    bag.Add(url);
                }
                catch (Exception ex)
                {
                    Logger.LogError($"{ex.Message}，{ex.StackTrace}");
                }
            }));

            return bag.Where(p => !string.IsNullOrEmpty(p)).ToList();
        }

        /// <summary>
        /// 获取报告Excel的Url
        /// </summary>
        /// <param name="taskId"></param>
        /// <returns></returns>
        public async ValueTask<string> GetReportUrl(Guid taskId)
        {
            var excelModeFile = Path.Combine(Env.WebRootPath, "SystemFiles", "ExcelTemplates", "实测汇总合格率统计表模板.xlsx");
            if (!File.Exists(excelModeFile))
            {
                Logger.LogInformation($"实测汇总合格率统计表模板.xlsx不存在");
                return null;
            }

            string reportUrl = null;
            var key = GetReportCacheKey(taskId);
            var isExists = RedisHelper.Instance.Exists(key);
            if (isExists)
            {
                reportUrl = RedisHelper.Instance.HashGet(key, "ReportUrl");
                var reportFile = RedisHelper.Instance.HashGet(key, "ReportFile");
                if (File.Exists(reportFile))
                    return reportUrl;
                else
                    DeleteReportCache(taskId);
            }

            var info = await MsTaskRepository.GetApartmentSunmaryInfo(taskId);
            if (info == null)
            {
                Logger.LogInformation($"任务报告数据不存在：TaskId = {taskId}");
                return null;
            }

            info.MeasureItems = info.Measuresummaryinfo.ToObject<List<MeasureItemSummaryInfo>>();
            info.CreateDate = DateTime.Parse(info.FinishTime).ToString("yyyy-MM-dd");

            var baseDir = Path.Combine(info.ResultDataFilePath, info.TaskName);
            var reportDir = Path.Combine(baseDir, "分户报告");
            if (!Directory.Exists(reportDir))
                Directory.CreateDirectory(reportDir);
            info.ImageFile = Path.Combine(baseDir, "任务户型图", $"{info.TaskId}.png");

            var reportSummaryFile = Path.Combine(reportDir, $"{info.TaskName}-{info.ProjectStage}-{info.TaskTypeName}-汇总报告.xlsx");
            var lockKey = $"task_apartmentreport_lock_{taskId}";
            if (RedisHelper.Instance.LockTake(lockKey, Environment.MachineName, TimeSpan.FromSeconds(10)))
            {
                try
                {
                    if (RedisHelper.Instance.Exists(key))
                    {
                        reportUrl = RedisHelper.Instance.HashGet(key, "ReportUrl");
                        return reportUrl;
                    }

                    if (File.Exists(reportSummaryFile))
                        File.Delete(reportSummaryFile);

                    var pointList = await MsTaskPointsRepository.GetPointMeasureResult(info.ProjectId, info.TaskId);
                    var apartmentList = pointList.Select(p => p.HouseTypeName).Distinct().OrderBy(p => p).ToList();
                    if (apartmentList.IsEmpty())
                    {
                        Logger.LogInformation($"导出实测汇总合格率统计表失败：此任务不存在分户数据");
                        return null;
                    }

                    IExportFileByTemplate exporter = new ExcelExporter();
                    await exporter.ExportByTemplate(reportSummaryFile, info, excelModeFile);

                    if (!File.Exists(reportSummaryFile))
                    {
                        Logger.LogError($"导出实测汇总合格率统计表失败：{reportSummaryFile}");
                        return null;
                    }

                    var usestandardInfo = info.UsestandardAllInfo.ToObject<UsestandardAllInfo>();

                    await ExportApartmentReport(pointList, info, usestandardInfo, baseDir, reportDir, reportSummaryFile);

                    await ExportNetHeightReport(pointList, info, usestandardInfo, reportDir, reportSummaryFile);

                    reportUrl = StatisService.ToTaskReportUrl(reportSummaryFile);

                    SetReportCache(key, reportUrl, reportSummaryFile);

                    DeleteTempFile(reportDir, reportSummaryFile);
                }
                catch (Exception ex)
                {
                    Logger.LogError($"{ex.Message}，{ex.StackTrace}", ex);
                }
                finally
                {
                    RedisHelper.Instance.LockRelease(lockKey, Environment.MachineName);
                }
            }

            return reportUrl;
        }

        /// <summary>
        /// 删除临时文件
        /// </summary>
        /// <param name="reportDir"></param>
        /// <param name="reportSummaryFile"></param>
        private void DeleteTempFile(string reportDir, string reportSummaryFile)
        {
            var fileList = Directory.GetFiles(reportDir).Where(p => p != reportSummaryFile).ToList();
            foreach (var item in fileList)
            {
                try
                {
                    File.Delete(item);
                }
                catch (Exception ex)
                {
                    Logger.LogError($"删除文件失败：{item}，ErrMessage：{ex.Message}");
                }
            }
        }

        /// <summary>
        /// 导出净高净距报告
        /// </summary>
        /// <param name="pointList"></param>
        /// <param name="info"></param>
        /// <param name="usestandardInfo"></param>
        /// <param name="reportDir"></param>
        /// <param name="reportSummaryFile"></param>
        /// <returns></returns>
        private async Task ExportNetHeightReport(List<PointMeasureResultInfo> pointList, MsApartmentSunmaryInfoDto info, UsestandardAllInfo usestandardInfo, string reportDir, string reportSummaryFile)
        {
            // 分户净高净距
            if (pointList.IsEmpty())
                return;

            var templateFile = Path.Combine(Env.WebRootPath, "SystemFiles", "ExcelTemplates", "分户净高净距报告模板.xlsx");
            if (!File.Exists(templateFile))
            {
                Logger.LogError($"分户净高净距报告模板.xlsx不存在");
                return;
            }

            // 净高标准值
            var netHeightStandardRangeItem = usestandardInfo.Items.FirstOrDefault(p => p.Code == "r-jinggao");
            var netHeightStandardRangeValue = netHeightStandardRangeItem?.Value == null ? "/" : $"[{string.Join(",", netHeightStandardRangeItem?.Value?.Select(p => p.ToInt()))}]";

            // 开间进深极差标准值
            var bayDepthRangeItem = usestandardInfo.Items.FirstOrDefault(p => p.Code == "r-kaijianjinshenjicha");
            var bayDepthRangeValue = bayDepthRangeItem?.Value == null ? "/" : $"[{string.Join(",", bayDepthRangeItem?.Value?.Select(p => p.ToInt()))}]";

            // 开间进深偏差标准值
            var bayDepthDeviationItem = usestandardInfo.Items.FirstOrDefault(p => p.Code == "r-kaijianjinshenpiancha");
            var bayDepthDeviationValue = bayDepthDeviationItem?.Value == null ? "/" : $"[{string.Join(",", bayDepthDeviationItem?.Value?.Select(p => p.ToInt()))}]";

            var apartmentList = pointList.Select(p => p.HouseTypeName).Distinct().OrderBy(p => p).ToList();
            foreach (var apart in apartmentList)
            {
                NetHeightWidthInfo netHeightWidthInfo = new NetHeightWidthInfo
                {
                    ProjectName = info.ProjectName,
                    ApartmentNumber = apart,
                    PointInfos = new List<PointMeasureValueInfo>()
                };
                var subPoints = pointList.Where(p => p.HouseTypeName == apart).OrderBy(p => p.Index).ToList();
                foreach (var pt in subPoints)
                {
                    var designList = pt.Robotmeasuredesign.ToObject<List<RobotMeasureDesignInfo>>();
                    // 净高设计值
                    var netHeightDesighInfo = designList?.FirstOrDefault(p => p.Code == "r-jinggao");
                    string netHeightDesighValue;
                    var netHeightValue = netHeightDesighInfo?.DesignValue?.Height;
                    netHeightDesighValue = netHeightValue.HasValue ? netHeightValue.ToString() : "/";

                    // 开间进深设计值
                    string bayDepthDesighValue = null;
                    var bayDepthDesighList = designList?.Where(p => p.Code == "r-kaijianjinshenpiancha").ToList();
                    if (bayDepthDesighList.IsEmpty())
                    {
                        bayDepthDesighValue = "/";
                    }
                    else
                    {
                        StringBuilder sb = new StringBuilder();
                        foreach (var item in bayDepthDesighList)
                        {
                            sb.AppendLine($"{item.DesignValue.Width},{item.DesignValue.Length}");
                        }
                        bayDepthDesighValue = sb.ToString();
                    }

                    // 净高测量值：优先显示一米线净高
                    NetHeightReportInfo netHeightReportInfo = null;
                    netHeightReportInfo = pt.Tackpointjson["onenetHeight"]?.ToObject<NetHeightReportInfo>();
                    if (netHeightReportInfo == null || netHeightReportInfo.Data.IsEmpty())
                    {
                        netHeightReportInfo = pt.Tackpointjson["netHeight"]?.ToObject<NetHeightReportInfo>();
                    }

                    // 开间进深测量值
                    var bayDepthReportInfo = pt.Tackpointjson["bayDepth"]?.ToObject<BayDepthReportInfo>();

                    netHeightWidthInfo.PointInfos.Add(new PointMeasureValueInfo
                    {
                        Index = pt.Index,
                        PointType = pt.PointTypeName,
                        NetHeightDesighValue = netHeightDesighValue,
                        BayDepthDesighValue = bayDepthDesighValue,
                        NetHeightStandardRange = netHeightStandardRangeValue,
                        BayDepthRange = bayDepthRangeValue,
                        BayDepthDeviation = bayDepthDeviationValue,
                        NetHeightActualH1 = GetNetHeightData(netHeightReportInfo, 1),
                        NetHeightActualH2 = GetNetHeightData(netHeightReportInfo, 2),
                        NetHeightActualH3 = GetNetHeightData(netHeightReportInfo, 3),
                        NetHeightActualH4 = GetNetHeightData(netHeightReportInfo, 4),
                        NetHeightActualH5 = GetNetHeightData(netHeightReportInfo, 5),
                        NetHeightActualH6 = GetNetHeightData(netHeightReportInfo, 6),
                        NetHeightActualH7 = GetNetHeightData(netHeightReportInfo, 7),
                        NetHeightActualH8 = GetNetHeightData(netHeightReportInfo, 8),
                        NetHeightActualH9 = GetNetHeightData(netHeightReportInfo, 9),
                        NetHeightActualH10 = GetNetHeightData(netHeightReportInfo, 10),
                        BayDepthRangeActualB0 = GetBayDepthRangeActualValue(bayDepthReportInfo, "B0"),
                        BayDepthRangeActualB1 = GetBayDepthRangeActualValue(bayDepthReportInfo, "B1"),
                        BayDepthRangeActualD0 = GetBayDepthRangeActualValue(bayDepthReportInfo, "D0"),
                        BayDepthRangeActualD1 = GetBayDepthRangeActualValue(bayDepthReportInfo, "D1"),
                        BayDepthDeviationActualB0_1 = GetBayDepthDeviationActualValue(bayDepthReportInfo, "B0_1"),
                        BayDepthDeviationActualB0_2 = GetBayDepthDeviationActualValue(bayDepthReportInfo, "B0_2"),
                        BayDepthDeviationActualB1_1 = GetBayDepthDeviationActualValue(bayDepthReportInfo, "B1_1"),
                        BayDepthDeviationActualB1_2 = GetBayDepthDeviationActualValue(bayDepthReportInfo, "B1_2"),
                        BayDepthDeviationActualD0_1 = GetBayDepthDeviationActualValue(bayDepthReportInfo, "D0_1"),
                        BayDepthDeviationActualD0_2 = GetBayDepthDeviationActualValue(bayDepthReportInfo, "D0_2"),
                        BayDepthDeviationActualD1_1 = GetBayDepthDeviationActualValue(bayDepthReportInfo, "D1_1"),
                        BayDepthDeviationActualD1_2 = GetBayDepthDeviationActualValue(bayDepthReportInfo, "D1_2"),
                    });
                }

                string bayDepthReportFile = Path.Combine(reportDir, $"{info.TaskName}-{apart}-分户净高净距报告.xlsx");

                //此导出方式是使用Magicodes.IE库，目前已使用下面的库
                //IExportFileByTemplate exporter = new ExcelExporter();
                //await exporter.ExportByTemplate(bayDepthReportFile, netHeightWidthInfo, templateFile);

                using var template = new XLTemplate(templateFile);

                // 将数据添加到模板  
                template.AddVariable("netHeightWidthInfo", netHeightWidthInfo);

                // 生成报告  
                template.Generate();

                // 获取工作簿  
                using var wk = template.Workbook;

                // 获取工作表  
                var ws = wk.Worksheet(1);

                template.SaveAs(bayDepthReportFile);

                DeleteNullValueColumn(bayDepthReportFile, netHeightWidthInfo);

                CopySheets(bayDepthReportFile, reportSummaryFile, $"分户{apart}净高净距");
            }
        }

        /// <summary>
        /// 删除空值的列
        /// </summary>
        /// <param name="bayDepthReportFile"></param>
        /// <param name="netHeightWidthInfo"></param>
        private void DeleteNullValueColumn(string bayDepthReportFile, NetHeightWidthInfo netHeightWidthInfo)
        {
            /*
             1、删除NetHeightActualH值为空的列
             2、NetHeightActualH在Excel模板中对应的列为第8列至17列，同时至少保持H1-H4的列
            */
            int startNumber = 4;
            var maxNumber = GetFieldMaxNumber(netHeightWidthInfo, "NetHeightActualH", startNumber);

            // 获取工作簿  
            using var wk = new XLWorkbook(bayDepthReportFile);

            // 获取工作表  
            var ws = wk.Worksheet(1);

            for (int i = 17; i >= 11 + maxNumber - startNumber + 1; i--)
            {
                ws.Column(i).Delete();
            }

            wk.Save();
        }

        /// <summary>
        /// 转化为1位小数的字符串
        /// </summary>
        /// <param name="netHeightReportInfo"></param>
        /// <param name="serial"></param>
        /// <returns></returns>
        private string GetNetHeightData(NetHeightReportInfo netHeightReportInfo, int serial)
        {
            var value = netHeightReportInfo?.Data?.FirstOrDefault(p => p.Serial == serial)?.Height;
            return value.HasValue ? Math.Round(value.Value, 1).ToString() : "/";
        }

        /// <summary>
        /// 获取平整度或垂直度的值
        /// </summary>
        /// <param name="wall"></param>
        /// <param name="key"></param>
        /// <returns></returns>
        private string GetFlatnessOrVerticalData(WallFlatVerticalInfo wall, string key)
        {
            var value = wall?.Data?.FirstOrDefault(p => p.Title == key)?.Value;
            return value.HasValue ? Math.Round(value.Value, 1).ToString() : "/";
        }

        /// <summary>
        /// 获取开间进深极差
        /// </summary>
        /// <param name="bayDepthReportInfo"></param>
        /// <param name="key"></param>
        /// <returns></returns>
        private string GetBayDepthRangeActualValue(BayDepthReportInfo bayDepthReportInfo, string key)
        {
            var value = bayDepthReportInfo?.Range?.FirstOrDefault(p => p.Key == key)?.Value;
            return string.IsNullOrEmpty(value) ? "/" : Math.Round(double.Parse(value), 1).ToString();
        }

        /// <summary>
        /// 获取开间进深偏差
        /// </summary>
        /// <param name="bayDepthReportInfo"></param>
        /// <param name="key"></param>
        /// <returns></returns>
        private string GetBayDepthDeviationActualValue(BayDepthReportInfo bayDepthReportInfo, string key)
        {
            var value = bayDepthReportInfo?.Deviation?.FirstOrDefault(p => p.Key == key)?.Value;
            return string.IsNullOrEmpty(value) ? "/" : Math.Round(double.Parse(value), 1).ToString();
        }

        /// <summary>
        /// 导出分户报告
        /// </summary>
        /// <param name="info"></param>
        /// <param name="baseDir"></param>
        /// <param name="reportDir"></param>
        /// <param name="reportSummaryFile"></param>
        /// <returns></returns>
        private async Task ExportApartmentReport(List<PointMeasureResultInfo> pointList, MsApartmentSunmaryInfoDto info, UsestandardAllInfo usestandardInfo, string baseDir, string reportDir, string reportSummaryFile)
        {
            // 分户报告
            if (pointList.IsEmpty())
                return;

            var templateFile = Path.Combine(Env.WebRootPath, "SystemFiles", "ExcelTemplates", "分户报告模板.xlsx");
            if (!File.Exists(templateFile))
            {
                Logger.LogError($"分户报告模板.xlsx不存在");
                return;
            }

            info.Craft = info.Craft == "0" || string.IsNullOrWhiteSpace(info.Craft) ? null : info.Craft;

            // 平整度
            var wallFlatStandardItem = usestandardInfo.Items.FirstOrDefault(p => p.Code == "r-pingzhengdu" && p.SupportCraft == info.Craft);
            var flatnessRange = wallFlatStandardItem?.Value == null ? null : $"[{string.Join(",", wallFlatStandardItem?.Value?.Select(p => p.ToInt()))}]{wallFlatStandardItem.Unit}";

            // 垂直度
            var verticalFlatStandardItem = usestandardInfo.Items.FirstOrDefault(p => p.Code == "r-chuizhidu" && p.SupportCraft == info.Craft);
            if (verticalFlatStandardItem == null && !string.IsNullOrWhiteSpace(info.Craft))
            {
                // 砌筑阶段“垂直度”工艺标准为null
                info.Craft = null;
                verticalFlatStandardItem = usestandardInfo.Items.FirstOrDefault(p => p.Code == "r-chuizhidu" && p.SupportCraft == info.Craft);
            }
            var verticalRange = verticalFlatStandardItem?.Value == null ? null : $"[{string.Join(",", verticalFlatStandardItem?.Value?.Select(p => p.ToInt()))}]{verticalFlatStandardItem.Unit}";

            var apartmentList = pointList.Select(p => p.HouseTypeName).Distinct().OrderBy(p => p).ToList();
            foreach (var apart in apartmentList)
            {
                ApartmentInfo apartmentInfo = new ApartmentInfo()
                {
                    ProjectName = info.ProjectName,
                    BuildingName = info.BuildingName,
                    FloorName = info.FloorName,
                    ApartmentNumber = apart,
                    CreateDate = info.CreateDate,
                    PointInfos = new List<PointInfo>()
                };

                var subPoints = pointList.Where(p => p.HouseTypeName == apart).OrderBy(p => p.Index).ToList();
                foreach (var pt in subPoints)
                {
                    PointInfo pointInfo = new PointInfo()
                    {
                        Index = pt.Index,
                        ImageId = $"ImageId{pt.Index}",
                        FlatnessRange = flatnessRange,
                        VerticalRange = verticalRange,
                        ImageFile = Path.Combine(baseDir, "热力图基础数据", $"{pt.TaskId}_{pt.Index}", "planform_cut.png"),
                        WallInfos = new List<WallInfo>()
                    };

                    if (!File.Exists(pointInfo.ImageFile))
                        pointInfo.ImageId = "没有图片";

                    var wallFlatList = pt.Tackpointjson["wall_flat"]?.ToObject<List<WallFlatVerticalInfo>>();
                    var verticalFlatList = pt.Tackpointjson["vertical_flat"]?.ToObject<List<WallFlatVerticalInfo>>();

                    List<string> titleList = new List<string>();
                    if (wallFlatList.IsNotEmpty())
                        titleList.AddRange(wallFlatList.Select(p => p.Title));
                    if (verticalFlatList.IsNotEmpty())
                        titleList.AddRange(verticalFlatList.Select(p => p.Title));

                    titleList = titleList.Distinct().ToList();

                    foreach (var t in titleList)
                    {
                        var wall = wallFlatList?.FirstOrDefault(p => p.Title == t);
                        var vertical = verticalFlatList?.FirstOrDefault(p => p.Title == t);
                        pointInfo.WallInfos.Add(new WallInfo
                        {
                            Name = t,
                            FlatnessR1 = GetFlatnessOrVerticalData(wall, "R1"),
                            FlatnessR2 = GetFlatnessOrVerticalData(wall, "R2"),
                            FlatnessR3 = GetFlatnessOrVerticalData(wall, "R3"),
                            FlatnessR4 = GetFlatnessOrVerticalData(wall, "R4"),
                            FlatnessR5 = GetFlatnessOrVerticalData(wall, "R5"),
                            FlatnessR6 = GetFlatnessOrVerticalData(wall, "R6"),
                            FlatnessR7 = GetFlatnessOrVerticalData(wall, "R7"),
                            FlatnessR8 = GetFlatnessOrVerticalData(wall, "R8"),
                            FlatnessR9 = GetFlatnessOrVerticalData(wall, "R9"),
                            FlatnessR10 = GetFlatnessOrVerticalData(wall, "R10"),
                            FlatnessR11 = GetFlatnessOrVerticalData(wall, "R11"),
                            FlatnessR12 = GetFlatnessOrVerticalData(wall, "R12"),
                            FlatnessR13 = GetFlatnessOrVerticalData(wall, "R13"),
                            FlatnessR14 = GetFlatnessOrVerticalData(wall, "R14"),
                            FlatnessR15 = GetFlatnessOrVerticalData(wall, "R15"),
                            FlatnessR16 = GetFlatnessOrVerticalData(wall, "R16"),
                            FlatnessR17 = GetFlatnessOrVerticalData(wall, "R17"),
                            FlatnessR18 = GetFlatnessOrVerticalData(wall, "R18"),
                            FlatnessR19 = GetFlatnessOrVerticalData(wall, "R19"),
                            FlatnessR20 = GetFlatnessOrVerticalData(wall, "R20"),
                            VerticalR1 = GetFlatnessOrVerticalData(vertical, "R1"),
                            VerticalR2 = GetFlatnessOrVerticalData(vertical, "R2"),
                            VerticalR3 = GetFlatnessOrVerticalData(vertical, "R3"),
                            VerticalR4 = GetFlatnessOrVerticalData(vertical, "R4"),
                            VerticalR5 = GetFlatnessOrVerticalData(vertical, "R5"),
                            VerticalR6 = GetFlatnessOrVerticalData(vertical, "R6"),
                            VerticalR7 = GetFlatnessOrVerticalData(vertical, "R7"),
                            VerticalR8 = GetFlatnessOrVerticalData(vertical, "R8"),
                            VerticalR9 = GetFlatnessOrVerticalData(vertical, "R9"),
                            VerticalR10 = GetFlatnessOrVerticalData(vertical, "R10"),
                            VerticalR11 = GetFlatnessOrVerticalData(vertical, "R11"),
                            VerticalR12 = GetFlatnessOrVerticalData(vertical, "R12"),
                            VerticalR13 = GetFlatnessOrVerticalData(vertical, "R13"),
                            VerticalR14 = GetFlatnessOrVerticalData(vertical, "R14"),
                            VerticalR15 = GetFlatnessOrVerticalData(vertical, "R15"),
                            VerticalR16 = GetFlatnessOrVerticalData(vertical, "R16"),
                            VerticalR17 = GetFlatnessOrVerticalData(vertical, "R17"),
                            VerticalR18 = GetFlatnessOrVerticalData(vertical, "R18"),
                            VerticalR19 = GetFlatnessOrVerticalData(vertical, "R19"),
                            VerticalR20 = GetFlatnessOrVerticalData(vertical, "R20"),
                        });
                    }

                    apartmentInfo.PointInfos.Add(pointInfo);
                }

                int startNumber = 6;
                var maxFlatnessR = GetFieldMaxNumber(apartmentInfo, "FlatnessR", startNumber);
                var maxVerticalR = GetFieldMaxNumber(apartmentInfo, "VerticalR", startNumber);

                using var template = new XLTemplate(templateFile);

                // 将数据添加到模板  
                template.AddVariable("apartments", apartmentInfo);

                // 生成报告  
                template.Generate();

                // 获取工作簿  
                using var wk = template.Workbook;

                // 获取工作表  
                var ws = wk.Worksheet(1);

                /*
                 1、删除VerticalR值为空的列
                 2、VerticalR在Excel模板中对应的列时第24列至43列，同时至少保持R1-R6的列
                */
                for (int i = 43; i >= 29 + maxVerticalR - startNumber + 1; i--)
                {
                    ws.Column(i).Delete();
                }

                /*
                 1、删除FlatnessR值为空的列，必须先删除VerticalR的列，后删除FlatnessR值的列
                 2、FlatnessR在Excel模板中对应的列时第4列至23列，同时至少保持R1-R6的列
                */
                for (int i = 23; i >= 9 + maxFlatnessR - startNumber + 1; i--)
                {
                    ws.Column(i).Delete();
                }

                //合并单元格
                int start_row = 4;
                foreach (var pt in apartmentInfo.PointInfos)
                {
                    int end_row = start_row + 2 + pt.WallInfos.Count();
                    ws.Range($"A{start_row}:B{end_row}").Merge();
                    start_row = end_row + 1;
                }

                // 保存文件
                string apartmentReportFile = Path.Combine(reportDir, $"{info.TaskName}-{apart}-分户报告.xlsx");
                template.SaveAs(apartmentReportFile);

                // 复制Sheet
                var sheetName = $"分户{apart}";
                CopySheets(apartmentReportFile, reportSummaryFile, sheetName);

                #region 将图片插入单元格

                // 注意：之所以要执行两步类似的步骤，是因为将图片插入分户报告Sheet的时候保存Excel的时候，汇总报告Sheet的图片会不翼而飞
                // 所以采用补救的方式，后续再重新将图片插入到汇总报告Sheet中

                // 遍历工作表的行和列，查找包含特定文本的单元格，并在该单元格中插入图片
                using var xlWorkBook = new XLWorkbook(reportSummaryFile);
                var apartSheet = xlWorkBook.Worksheet(sheetName);
                InsertImagesToApartmentSheet(apartSheet, apartmentInfo);

                // 遍历工作表的行和列，查找包含特定文本的单元格，并在该单元格中插入图片
                var sumSheet = xlWorkBook.Worksheets.First();
                InsertImagesToSummaySheet(sumSheet, info.ImageFile);
                xlWorkBook.Save();

                #endregion
            }
        }

        /// <summary>
        /// 获取字段末尾的最大数值
        /// </summary>
        /// <param name="apartmentInfo"></param>
        /// <param name="field"></param>
        /// <param name="startNumber"></param>
        /// <returns></returns>
        public int GetFieldMaxNumber(ApartmentInfo apartmentInfo, string field, int startNumber)
        {
            int maxNumber = startNumber;

            foreach (var pointInfo in apartmentInfo.PointInfos)
            {
                foreach (var wallInfo in pointInfo.WallInfos)
                {
                    // 获取WallInfo中所有以field开头的属性  
                    var properties = wallInfo.GetType().GetProperties().Where(p => p.Name.StartsWith(field)).ToList();

                    foreach (var property in properties)
                    {
                        // 获取属性的值  
                        var value = property.GetValue(wallInfo);
                        if (!string.IsNullOrEmpty(value?.ToString()) && value.ToString() != "/")
                        {
                            // 提取属性名中的数字  
                            int number = int.Parse(Regex.Match(property.Name, @"\d+").Value);

                            // 更新最大数字  
                            if (number > maxNumber)
                            {
                                maxNumber = number;
                            }
                        }
                    }
                }
            }

            return maxNumber;
        }

        /// <summary>
        /// 获取字段末尾的最大数值
        /// </summary>
        /// <param name="netHeightWidthInfo"></param>
        /// <param name="field"></param>
        /// <param name="startNumber"></param>
        /// <returns></returns>
        public int GetFieldMaxNumber(NetHeightWidthInfo netHeightWidthInfo, string field, int startNumber)
        {
            int maxNumber = startNumber;

            foreach (var pointInfo in netHeightWidthInfo.PointInfos)
            {
                // 获取PointInfo所有以field开头的属性  
                var properties = pointInfo.GetType().GetProperties()
                    .Where(p => p.Name.StartsWith(field)).ToList();

                foreach (var property in properties)
                {
                    // 获取属性的值  
                    var value = property.GetValue(pointInfo);
                    if (!string.IsNullOrEmpty(value?.ToString()) && value.ToString() != "/")
                    {
                        // 提取属性名中的数字  
                        int number = int.Parse(Regex.Match(property.Name, @"\d+").Value);

                        // 更新最大数字  
                        if (number > maxNumber)
                        {
                            maxNumber = number;
                        }
                    }
                }
            }

            return maxNumber;
        }

        /// <summary>
        /// 复制第1个Excel文件的Sheet到第2个Excel文件
        /// </summary>
        /// <param name="excel_target"></param>
        /// <param name="excel_source"></param>
        /// <param name="newSheetName"></param>
        public void CopySheets(string excel_source, string excel_target, string newSheetName)
        {
            // 读取excel文件  
            using FileStream file_source = File.OpenRead(excel_source);
            using FileStream file_target = File.OpenRead(excel_target);

            using IWorkbook workbook_source = new XSSFWorkbook(file_source);
            using IWorkbook workbook_target = new XSSFWorkbook(file_target);

            // 复制 excel_source 的 sheet 到 excel_target  
            for (int i = 0; i < workbook_source.NumberOfSheets; i++)
            {
                ISheet sheet_source = workbook_source.GetSheetAt(i);

                newSheetName = i == 0 ? newSheetName : $"{newSheetName}_{i}";

                sheet_source.CopyTo(workbook_target, newSheetName, true, true);
            }

            // 保存 excel_target 文件  
            using FileStream output = new FileStream(excel_target, FileMode.Create, FileAccess.Write, FileShare.Delete);
            workbook_target.Write(output, false);

            //.net core3.1版本output释放的比较慢，后续容易出现文件占用的情况，手动释放可解决该问题
            output?.Dispose();
        }

        /// <summary>
        /// 插入图片到分户报告Sheet
        /// </summary>
        /// <param name="ws"></param>
        /// <param name="apartInfo"></param>
        private void InsertImagesToApartmentSheet(IXLWorksheet ws, ApartmentInfo apartInfo)
        {
            // 遍历工作表的行和列  
            for (int rowIndex = 1; rowIndex <= ws.LastRowUsed().RowNumber(); rowIndex++)
            {
                for (int colIndex = 1; colIndex <= ws.LastColumnUsed().ColumnNumber(); colIndex++)
                {
                    // 获取当前单元格  
                    var cell = ws.Cell(rowIndex, colIndex);

                    // 检查单元格的值是否以 "ImageId"开始  
                    if (cell.Value != null && cell.Value.ToString().StartsWith("ImageId"))
                    {
                        var pointInfo = apartInfo.PointInfos.FirstOrDefault(p => p.ImageId == cell.Value.ToString());
                        if (pointInfo != null)
                        {
                            InsertImageToCell(ws, pointInfo.ImageFile, cell, 300, 300, rowIndex, 260);
                        }
                    }
                }
            }
        }

        /// <summary>
        /// 插入图片到汇总报告Sheet
        /// </summary>
        /// <param name="ws"></param>
        /// <param name="imageFile"></param>
        private void InsertImagesToSummaySheet(IXLWorksheet ws, string imageFile)
        {
            // 遍历工作表的行和列  
            for (int rowIndex = 1; rowIndex <= ws.LastRowUsed().RowNumber(); rowIndex++)
            {
                for (int colIndex = 1; colIndex <= ws.LastColumnUsed().ColumnNumber(); colIndex++)
                {
                    // 获取当前单元格  
                    var cell = ws.Cell(rowIndex, colIndex);

                    // 检查单元格的值是否等于"ImageId0"
                    if (cell.Value != null && cell.Value.ToString() == "ImageId0")
                    {
                        InsertImageToCell(ws, imageFile, cell, 600, 300, rowIndex);
                    }
                }
            }
        }

        /// <summary>
        /// 插入图片到单元格
        /// </summary>
        /// <param name="ws"></param>
        /// <param name="imageFile"></param>
        /// <param name="cell"></param>
        /// <param name="maxWidth"></param>
        /// <param name="maxHeight"></param>
        /// <param name="rowIndex"></param>
        /// <param name="rowHeight"></param>
        private void InsertImageToCell(IXLWorksheet ws, string imageFile, IXLCell cell, int maxWidth, int maxHeight, int rowIndex, int? rowHeight = null)
        {
            if (rowHeight != null)
                ws.Row(rowIndex).Height = rowHeight.Value;

            // 将图片插入到单元格中  
            var imageBytes = File.ReadAllBytes(imageFile);
            using var imageStream = new MemoryStream(imageBytes);

            // 清空单元格中的值  
            cell.Value = "";

            // 将图片添加到工作表上并设置图片的位置  
            var picture = ws.AddPicture(imageStream, XLPictureFormat.Png);
            picture.MoveTo(cell);

            // 根据缩放因子设置了图片的宽度和高度  
            double scaleFactor = Math.Min((double)maxWidth / picture.Width, (double)maxHeight / picture.Height);
            picture.Width = Convert.ToInt32(picture.Width * scaleFactor);
            picture.Height = Convert.ToInt32(picture.Height * scaleFactor);

            // 计算合并单元格的宽度和高度  
            double totalWidthInPixels = 0;
            double totalHeightInPixels = 0;

            if (cell.IsMerged())
            {
                var mergedRange = cell.MergedRange();
                for (int i = mergedRange.FirstColumn().ColumnNumber(); i <= mergedRange.LastColumn().ColumnNumber(); i++)
                {
                    totalWidthInPixels += ws.Column(i).Width * 7;
                }
                for (int i = mergedRange.FirstRow().RowNumber(); i <= mergedRange.LastRow().RowNumber(); i++)
                {
                    totalHeightInPixels += ws.Row(i).Height / 0.75;
                }
            }
            else
            {
                totalWidthInPixels = cell.WorksheetColumn().Width * 7;
                totalHeightInPixels = cell.WorksheetRow().Height / 0.75;
            }

            // 计算图片居中的偏移量  
            int offsetX = Convert.ToInt32((totalWidthInPixels - picture.Width) / 2);
            int offsetY = Convert.ToInt32((totalHeightInPixels - picture.Height) / 2);

            // 将图片移动到单元格并应用偏移量  
            picture.MoveTo(cell, offsetX, offsetY);
        }
    }
}
